const express = require('express');

const routes = express.Router();

//引入mysql
const mysql = require('mysql');
//创建连接池
var pool = mysql.createPool({
    connectionLimit : 10,
    host : 'localhost',
    user : 'root',
    password : 'root',
    database : 'stumsg'
})

routes.route('/')
    .get( (req,resp)=>{

        //获取cookies
        var uid = req.cookies.uid;
        var gender = req.cookies.gender;

        //判断
        if(gender == 1){
            var pname = '教师';
        }else {
            var pname = '学生';

        }

        resp.render('sel_scores',{
            uid : uid,
            pname : pname,
        })
    } )
    .post( (req,resp)=>{
        //获取Ajax提交过来的数据  做专业对比较
        var sel = req.body.num;
        // console.log(sel)
    //    获取cookies  当前教师专业的id
        var sub_id = req.cookies.sub_id;
    //    sql语句
        var sql = `SELECT scores.scores , scores.uid , stu_list.stu_name, sub_class.class_name, sub_class.sub_id, sub_class.num FROM scores LEFT JOIN stu_list ON scores.uid = stu_list.uid LEFT JOIN sub_class ON sub_class.id = scores.class_id WHERE sub_class.sub_id = ${sub_id} AND sub_class.num = ${sel}`;
        //console.log(sql)
    //    执行sql语句
        pool.getConnection( (err , connection)=>{
            if (err) throw err;
            connection.query( sql, (error , result)=>{

                //去重  查询多少个学生学号
                var sql_uid = `SELECT DISTINCT scores.uid FROM scores LEFT JOIN sub_class ON scores.class_id = sub_class.id WHERE sub_class.sub_id = ${sub_id} AND sub_class.num = ${sel}`;
                connection.query( sql_uid, (err , res)=>{
                    //    释放连接
                    connection.release();

                    //重组一个二维数组
                    var scores = [];
                    //循环学期成绩表
                    result.forEach( (list)=>{
                    //    循环学生学号
                        res.forEach( (item , index)=>{
                        //    判断下表生成二维数组
                            if( !scores[index] ) scores[index] = [];
                        //    根据对应条件  将数据添加到改下标的二维数组
                            if( list.uid == item.uid ){
                                scores[index].push( list )
                            }
                        } )
                    } )

                    resp.send( scores )
                    //console.log(scores)
                    if (err) throw err;
                } )

                //ajax的数据是直接传递给html的  所以不需要渲染模板和值对值的传递方式
                //resp.send( result );

                if(error) throw error;
            } )
        } )
    } )

//暴露接口
module.exports = routes;